# 创建 DuckDB 数据库表的通用函数
from duckdb_helper import DuckDBHelper
import pandas as pd
from datetime import datetime,timedelta
from mssql import query_sqlserver_df
from logger import logging

def import_duckdb_from_mssql(sql:str, sheet_name:str, pk_col:str)->None:
    """
    查询 SQL Server 数据，并insert到 DuckDB 表中
    :param sql: SQL Server 查询语句
    :param sheet_name: DuckDB 表名
    :param pk_col: 插入表的主键字段名（如有，避免重复导入）
    :return: None
    """
    # 查询 SQL Server 字段名和数据
    df = query_sqlserver_df(sql)
    if df is None or df.empty:
        logging.warning(f"查询结果为空。sql: {sql}")
        raise RuntimeError("SQL Server 无数据，无法插入表")    

   
    dbh = DuckDBHelper()
    try:
        dbh.batch_insert_df(df, sheet_name, pk_col)
        logging.info(f"DuckDB 表 {sheet_name} 插入数据完成")
    except Exception as e:
        logging.error(f"DuckDB 表 {sheet_name} 插入数据失败: {e}")
        raise


def import_zy_brry(start_date:str=(datetime.now() - timedelta(days=365)).strftime('%Y-%m-%d'), end_date:str=datetime.now().strftime('%Y-%m-%d %H:%M:%S'))->None:
    """
    从mssql中倒入原始住院病人信息表
    :param start_date: 起始日期
    :param end_date: 结束日期
    :return: None
    """
    sql = f"""
        SELECT * FROM [dz_his].[dbo].[zy_brry]
        WHERE ryrq >= '{start_date}' AND ryrq <= '{end_date}'
    """
    import_duckdb_from_mssql(sql, "zy_brry", pk_col="ZYH")


def import_zy_zkjl(start_date:str=(datetime.now() - timedelta(days=365)).strftime('%Y-%m-%d'), end_date:str=datetime.now().strftime('%Y-%m-%d %H:%M:%S'))->None:
    """
    从mssql中倒入原始转科记录信息表
    :param start_date: 起始日期
    :param end_date: 结束日期
    :return: None
    """
    sql = f"""
        SELECT * FROM [dz_his].[dbo].[zy_zkjl]
        WHERE bqzxrq >= '{start_date}' AND bqzxrq <= '{end_date}'
    """
    import_duckdb_from_mssql(sql, "zy_zkjl", pk_col="JLXH")


def import_ys_zy_jbzd(start_date:str=(datetime.now() - timedelta(days=365)).strftime('%Y-%m-%d'), end_date:str=datetime.now().strftime('%Y-%m-%d %H:%M:%S'))->None:
    """
    从mssql中倒入原始住院诊断记录信息表
    :param start_date: 起始日期
    :param end_date: 结束日期
    :return: None
    """
    sql = f"""
        SELECT * FROM [dz_his].[dbo].[ys_zy_jbzd]
        WHERE zdsj >= '{start_date}' AND zdsj <= '{end_date}'
    """
    import_duckdb_from_mssql(sql, "ys_zy_jbzd", pk_col="JLBH")


def import_gy_ksdm()->None:
    """
    从mssql中倒入原始科室信息表
    :param start_date: 起始日期
    :param end_date: 结束日期
    :return: None
    """
    sql = f"""
        SELECT * FROM [dz_his].[dbo].[gy_ksdm]        
    """
    import_duckdb_from_mssql(sql, "gy_ksdm", pk_col="KSDM")


def import_gy_jbbm()->None:
    """
    从mssql中倒入原始住院诊断记录信息表
    :param start_date: 起始日期
    :param end_date: 结束日期
    :return: None
    """
    sql = f"""
        SELECT * FROM [dz_his].[dbo].[gy_jbbm]        
    """
    import_duckdb_from_mssql(sql, "gy_jbbm", pk_col="JBXH")


def import_v_emr_yw_jzjl(start_date:str=(datetime.now() - timedelta(days=365)).strftime('%Y-%m-%d'), end_date:str=datetime.now().strftime('%Y-%m-%d %H:%M:%S'))->None:
    """
    从mssql中倒入出院信息表
    :param start_date: 起始日期
    :param end_date: 结束日期
    :return: None
    """
    sheet_name = "V_EMR_YW_JZJL"
    sql = f"""
        SELECT * FROM [dz_his].[dbo].[{sheet_name} where ryrq between '{start_date}' and '{end_date}']        
    """
    import_duckdb_from_mssql(sql, sheet_name, pk_col="JZHM")


def import_ys_zy_jzjl(start_date:str=(datetime.now() - timedelta(days=365)).strftime('%Y-%m-%d'), end_date:str=datetime.now().strftime('%Y-%m-%d %H:%M:%S'))->None:
    """
    从mssql中倒入出院信息表
    :param start_date: 起始日期
    :param end_date: 结束日期
    :return: None
    """
    sheet_name = "YS_ZY_JZJL"
    sql = f"""
        SELECT * FROM [dz_his].[dbo].[{sheet_name}] where ryrq between '{start_date}' and '{end_date}'       
    """
    import_duckdb_from_mssql(sql, sheet_name, pk_col="JZHM")



if __name__ == "__main__":
    import_zy_brry()
    import_zy_zkjl()
    import_ys_zy_jbzd()
    # import_gy_ksdm()
    # import_gy_jbbm()
    # import_v_emr_yw_jzjl(start_date='2028-1-1')
    import_ys_zy_jzjl(start_date='2018-1-1')


